import sqlite3
import os

# 数据库文件路径
DB_PATH = os.path.join(os.path.dirname(__file__), "fault_report.db")

def get_db_connection():
    """获取数据库连接"""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def init_database():
    """初始化数据库表"""
    conn = get_db_connection()
    cursor = conn.cursor()
    
    # 创建故障明细表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS fault_details (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            serial_number TEXT,
            device_name TEXT,
            resource_pool TEXT,
            phase TEXT,
            fault_time DATETIME,
            alarm_clear_time DATETIME,
            device_category TEXT,
            manufacturer TEXT,
            component_type TEXT,
            discovery_method TEXT,
            solution_record TEXT,
            is_domestic TEXT,
            component_sn TEXT
        )
    ''')
    
    # 创建故障数量统计表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS fault_counts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            phase TEXT,
            hardware_resource_pool TEXT,
            software_manufacturer TEXT,
            fault_handling_count INTEGER,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # 创建厂家故障统计表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS manufacturer_stats (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            manufacturer TEXT,
            device_type TEXT,
            optical_module INTEGER,
            cpu_non_domestic INTEGER,
            cpu_domestic_hygon INTEGER,
            cpu_domestic_arm INTEGER,
            memory INTEGER,
            disk_non_domestic INTEGER,
            disk_domestic INTEGER,
            cache_disk INTEGER,
            backplane INTEGER,
            nic_non_domestic INTEGER,
            nic_domestic INTEGER,
            sensor INTEGER,
            riser_card INTEGER,
            pcie_card_non_domestic INTEGER,
            pcie_card_domestic INTEGER,
            motherboard INTEGER,
            power_supply INTEGER,
            raid_card_non_domestic INTEGER,
            raid_card_domestic INTEGER,
            fiber INTEGER,
            fan_non_domestic INTEGER,
            fan_domestic INTEGER,
            whole_machine INTEGER,
            total_count INTEGER,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # 创建详细故障描述表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS fault_descriptions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            description TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # 创建邮件日志表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS system_logs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            message TEXT,
            level TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    conn.commit()
    conn.close()

def insert_fault_details(fault_data):
    """插入故障明细数据"""
    conn = get_db_connection()
    cursor = conn.cursor()
    
    cursor.execute('''
        INSERT INTO fault_details 
        (serial_number, device_name, resource_pool, phase, fault_time, alarm_clear_time, device_category, manufacturer, component_type, discovery_method, solution_record, is_domestic, component_sn)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ''', fault_data)
    
    conn.commit()
    conn.close()

def insert_fault_counts(count_data):
    """插入故障数量统计数据"""
    conn = get_db_connection()
    cursor = conn.cursor()
    
    cursor.execute('''
        INSERT INTO fault_counts 
        (phase, hardware_resource_pool, software_manufacturer, fault_handling_count)
        VALUES (?, ?, ?, ?)
    ''', count_data)
    
    conn.commit()
    conn.close()

def insert_manufacturer_stats(stats_data):
    """插入厂家故障统计数据"""
    conn = get_db_connection()
    cursor = conn.cursor()
    
    cursor.execute('''
        INSERT INTO manufacturer_stats 
        (manufacturer, device_type, optical_module, cpu_non_domestic, cpu_domestic_hygon, cpu_domestic_arm, memory, disk_non_domestic, disk_domestic, cache_disk, backplane, nic_non_domestic, nic_domestic, sensor, riser_card, pcie_card_non_domestic, pcie_card_domestic, motherboard, power_supply, raid_card_non_domestic, raid_card_domestic, fiber, fan_non_domestic, fan_domestic, whole_machine, total_count)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ''', stats_data)
    
    conn.commit()
    conn.close()

def insert_fault_description(description):
    """插入详细故障描述"""
    conn = get_db_connection()
    cursor = conn.cursor()
    
    cursor.execute('''
        INSERT INTO fault_descriptions 
        (description)
        VALUES (?)
    ''', (description,))
    
    conn.commit()
    conn.close()